﻿using System;
using System.Collections.Generic;
using System.Data;
using WebStickyNotes.Contracts;
using WebStickyNotes.Contracts.Interfaces;
using MySql.Data.MySqlClient;

namespace WebStickyNotes.Data.Access
{
	/// <summary>
	/// Needs correct implementation
	/// </summary>
	public class MySqlDataAccess : IWebStickyNotesDataAccess
	{
		private readonly MySqlConnection _dbConnection;

		public MySqlDataAccess(string connection)
        {
			_dbConnection = new MySqlConnection(connection);
            _dbConnection.Open();
        }

        public List<TaskDetail> GetTasksForSearch(string searchTerm)
        {
            var dbCommand = new MySqlCommand(
                "SELECT TaskDetail.Id, TaskDetail.Description, TaskDetail.Title, TaskDetail.TaskStreamId, TaskDetail.TaskPersonId, TaskDetail.TaskPriorityId, TaskDetail.Release, TaskDetail.PercentageComplete, TaskDetail.MilestoneDate, TaskDetail.TotalEffort, TaskStream.TaskProjectId FROM (((TaskDetail INNER JOIN TaskStream ON TaskStream.Id = TaskDetail.TaskStreamId) INNER JOIN TaskPriority ON TaskPriority.Id = TaskDetail.TaskPriorityId) INNER JOIN TaskPerson ON TaskPerson.Id = TaskDetail.TaskPersonId) INNER JOIN TaskProject ON TaskProject.Id = TaskStream.TaskProjectId WHERE TaskDetail.Description LIKE @SearchTerm OR TaskDetail.Title LIKE @SearchTerm OR TaskPriority.Title LIKE @SearchTerm OR TaskStream.Title LIKE @SearchTerm OR TaskPerson.FirstName LIKE @SearchTerm OR TaskPerson.LastName LIKE @SearchTerm OR TaskProject.Title LIKE @SearchTerm ORDER BY TaskDetail.MileStoneDate ASC, TaskPriority.Importance DESC",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("SearchTerm", searchTerm.Replace('*', '%')));

            var dataReader = dbCommand.ExecuteReader();

            return GetTaskDetailFromReader(dataReader);
        }

        public List<TaskProject> GetAllTaskProjects()
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskProject ORDER BY OrderBy ASC",
                _dbConnection);

            var dataReader = dbCommand.ExecuteReader();

            return GetTaskProjectsFromReader(dataReader);
        }

        public List<TaskProject> GetTaskProjectsForPerson(int taskPersonId)
        {
            var dbCommand = new MySqlCommand(
                "SELECT TaskProject.* FROM TaskProject INNER JOIN LinkProjectToPerson ON LinkProjectToPerson.TaskProjectId = TaskProject.Id WHERE LinkProjectToPerson.TaskPersonId = @PersonId ORDER BY OrderBy ASC",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("PersonId", taskPersonId));

            var dataReader = dbCommand.ExecuteReader();

            return GetTaskProjectsFromReader(dataReader);
        }

        public List<TaskStream> GetAllTaskStreams()
        {
            var dbCommand = new MySqlCommand(
                "SELECT TaskStream.*, TaskProject.Title `TaskProjectTitle` FROM (TaskStream INNER JOIN TaskProject ON TaskProject.Id = TaskStream.TaskProjectId) ORDER BY TaskStream.OrderBy ASC",
                _dbConnection);

            var dataReader = dbCommand.ExecuteReader();

            var taskStreams = GetTaskStreamsFromReader(dataReader);

            return taskStreams;
        }

        public List<TaskDetail> GetAllTaskDetails()
        {
            var dbCommand = new MySqlCommand(
                "SELECT TaskDetail.Id, TaskDetail.Description, TaskDetail.Title, TaskDetail.TaskStreamId, TaskDetail.TaskPersonId, TaskDetail.TaskPriorityId, TaskDetail.Release, TaskDetail.PercentageComplete, TaskDetail.MilestoneDate, TaskDetail.TotalEffort, TaskStream.TaskProjectId FROM ((TaskDetail INNER JOIN TaskPriority ON TaskPriority.Id = TaskDetail.TaskPriorityId) INNER JOIN TaskStream ON TaskStream.Id = TaskDetail.TaskStreamId) ORDER BY TaskDetail.MileStoneDate ASC, TaskPriority.Importance DESC",
                _dbConnection);

            var dataReader = dbCommand.ExecuteReader();

            return GetTaskDetailFromReader(dataReader);
        }

        public List<TaskPerson> GetAllTaskPeople()
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskPerson ORDER BY Email ASC",
                _dbConnection);

            var dataReader = dbCommand.ExecuteReader();

            var taskPeople = GetTaskPeopleFromReader(dataReader);

            return taskPeople;
        }

        public List<TaskPerson> GetPeopleByRole(int taskRoleId)
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskPerson WHERE TaskRoleId = @TaskRoleId ORDER BY Email ASC",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("TaskRoleId", taskRoleId));

            var dataReader = dbCommand.ExecuteReader();

            var taskPeople = GetTaskPeopleFromReader(dataReader);

            return taskPeople;
        }

        public List<TaskPriority> GetAllTaskPriorities()
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskPriority ORDER BY Importance DESC",
                _dbConnection);

            var dataReader = dbCommand.ExecuteReader();

            var taskPriorities = GetTaskPrioritiesFromReader(dataReader);

            return taskPriorities;
        }

        public List<TaskRole> GetAllTaskRoles()
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskRole",
                _dbConnection);

            var dataReader = dbCommand.ExecuteReader();

            var taskRoles = GetTaskRolesFromReader(dataReader);

            return taskRoles;
        }

        public List<TaskStream> GetStreamsByProject(int taskProjectId)
        {
            var dbCommand = new MySqlCommand(
                "SELECT TaskStream.*, TaskProject.Title `TaskProjectTitle` FROM (TaskStream INNER JOIN TaskProject ON TaskProject.Id = TaskStream.TaskProjectId) WHERE TaskStream.TaskProjectId = @TaskProjectId ORDER BY TaskStream.OrderBy ASC",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("TaskProjectId", taskProjectId));

            var dataReader = dbCommand.ExecuteReader();

            var taskStreams = GetTaskStreamsFromReader(dataReader);

            return taskStreams;
        }

        public List<TaskDetail> GetTasksByProject(int taskProjectId)
        {
            var dbCommand = new MySqlCommand(
                "SELECT TaskDetail.Id, TaskDetail.Description, TaskDetail.Title, TaskDetail.TaskStreamId, TaskDetail.TaskPersonId, TaskDetail.TaskPriorityId, TaskDetail.Release, TaskDetail.PercentageComplete, TaskDetail.MilestoneDate, TaskDetail.TotalEffort, TaskStream.TaskProjectId FROM (TaskDetail INNER JOIN TaskPriority ON TaskPriority.Id = TaskDetail.TaskPriorityId) INNER JOIN TaskStream ON TaskStream.Id = TaskDetail.TaskStreamId WHERE TaskStream.TaskProjectId = @TaskProjectId ORDER BY TaskDetail.MileStoneDate ASC, TaskPriority.Importance DESC",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("TaskProjectId", taskProjectId));

            var dataReader = dbCommand.ExecuteReader();

            return GetTaskDetailFromReader(dataReader);
        }

        public List<TaskDetail> GetTasksByStream(int taskStreamId)
        {
            var dbCommand = new MySqlCommand(
                "SELECT TaskDetail.Id, TaskDetail.Description, TaskDetail.Title, TaskDetail.TaskStreamId, TaskDetail.TaskPersonId, TaskDetail.TaskPriorityId, TaskDetail.Release, TaskDetail.PercentageComplete, TaskDetail.MilestoneDate, TaskDetail.TotalEffort, TaskStream.TaskProjectId FROM ((TaskDetail INNER JOIN TaskPriority ON TaskPriority.Id = TaskDetail.TaskPriorityId) INNER JOIN TaskStream ON TaskStream.Id = TaskDetail.TaskStreamId) WHERE TaskStreamId = @TaskStreamId ORDER BY TaskDetail.MileStoneDate ASC, TaskPriority.Importance DESC",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("TaskStreamId", taskStreamId));

            var dataReader = dbCommand.ExecuteReader();

            return GetTaskDetailFromReader(dataReader);
        }

        public List<TaskDetail> GetTasksByPerson(int taskPersonId)
        {
            var dbCommand = new MySqlCommand(
                "SELECT TaskDetail.Id, TaskDetail.Description, TaskDetail.Title, TaskDetail.TaskStreamId, TaskDetail.TaskPersonId, TaskDetail.TaskPriorityId, TaskDetail.Release, TaskDetail.PercentageComplete, TaskDetail.MilestoneDate, TaskDetail.TotalEffort, TaskStream.TaskProjectId FROM ((TaskDetail INNER JOIN TaskPriority ON TaskPriority.Id = TaskDetail.TaskPriorityId) INNER JOIN TaskStream ON TaskStream.Id = TaskDetail.TaskStreamId) WHERE TaskDetail.TaskPersonId = @TaskPersonId ORDER BY TaskDetail.MileStoneDate ASC, TaskPriority.Importance DESC",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("TaskPersonId", taskPersonId));

            var dataReader = dbCommand.ExecuteReader();

            return GetTaskDetailFromReader(dataReader);
        }

        public List<TaskDetail> GetTasksByPriority(int taskPriorityId)
        {
            var dbCommand = new MySqlCommand(
                "SELECT TaskDetail.Id, TaskDetail.Description, TaskDetail.Title, TaskDetail.TaskStreamId, TaskDetail.TaskPersonId, TaskDetail.TaskPriorityId, TaskDetail.Release, TaskDetail.PercentageComplete, TaskDetail.MilestoneDate, TaskDetail.TotalEffort, TaskStream.TaskProjectId FROM ((TaskDetail INNER JOIN TaskPriority ON TaskPriority.Id = TaskDetail.TaskPriorityId) INNER JOIN TaskStream ON TaskStream.Id = TaskDetail.TaskStreamId) WHERE TaskDetail.TaskPriorityId = @TaskPriorityId ORDER BY TaskDetail.MileStoneDate ASC",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("TaskPriorityId", taskPriorityId));

            var dataReader = dbCommand.ExecuteReader();

            return GetTaskDetailFromReader(dataReader);
        }

        public int SaveTaskProgress(TaskProgress taskProgress)
        {
            var dbCommand = new MySqlCommand(
                "INSERT INTO TaskProgress (ProgressDate, TaskDetailId, TaskProjectId, TaskPersonId, StartPercentage, EndPercentage, TotalEffort, StartEffort, EndEffort, PercentageChange, EffortChange) VALUES (@ProgressDate, @TaskDetailId, @TaskProjectId, @TaskPersonId, @StartPercentage, @EndPercentage, @TotalEffort, @StartEffort, @EndEffort, @PercentageChange, @EffortChange)",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("ProgressDate", taskProgress.ProgressDate.ToString("yyyy-MM-dd")));
            dbCommand.Parameters.Add(new MySqlParameter("TaskDetailId", taskProgress.TaskDetailId));
            dbCommand.Parameters.Add(new MySqlParameter("TaskProjectId", taskProgress.TaskProjectId));
            dbCommand.Parameters.Add(new MySqlParameter("TaskPersonId", taskProgress.TaskPersonId));
            dbCommand.Parameters.Add(new MySqlParameter("StartPercentage", taskProgress.StartPercentage));
            dbCommand.Parameters.Add(new MySqlParameter("EndPercentage", taskProgress.EndPercentage));
            dbCommand.Parameters.Add(new MySqlParameter("TotalEffort", taskProgress.TotalEffort));
            dbCommand.Parameters.Add(new MySqlParameter("StartEffort", taskProgress.StartEffort));
            dbCommand.Parameters.Add(new MySqlParameter("EndEffort", taskProgress.EndEffort));
            dbCommand.Parameters.Add(new MySqlParameter("PercentageChange", taskProgress.PercentageChange));
            dbCommand.Parameters.Add(new MySqlParameter("EffortChange", taskProgress.EffortChange));

            dbCommand.ExecuteNonQuery();

            dbCommand.CommandText = "SELECT @@Identity";

            return (int)dbCommand.ExecuteScalar();
        }

        public int SaveTaskDetail(TaskDetail taskDetail)
        {
            if (taskDetail.Release == null)
            {
                taskDetail.Release = string.Empty;
            }

            if (taskDetail.Id == 0)
            {
                var dbCommand = new MySqlCommand(
                    "INSERT INTO TaskDetail (Description, MilestoneDate, PercentageComplete, Release, TaskPersonId, TaskPriorityId, TaskStreamId, Title, TotalEffort) VALUES (@Description, @MilestoneDate, @PercentageComplete, @Release, @TaskPersonId, @TaskPriorityId, @TaskStreamId, @Title, @TotalEffort)",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Description", taskDetail.Description));
                dbCommand.Parameters.Add(new MySqlParameter("MilestoneDate", taskDetail.MilestoneDate.ToString("yyyy-MM-dd")));
                dbCommand.Parameters.Add(new MySqlParameter("PercentageComplete", taskDetail.PercentageComplete));
                dbCommand.Parameters.Add(new MySqlParameter("Release", taskDetail.Release));
                dbCommand.Parameters.Add(new MySqlParameter("TaskPersonId", taskDetail.TaskPersonId));
                dbCommand.Parameters.Add(new MySqlParameter("TaskPriorityId", taskDetail.TaskPriorityId));
                dbCommand.Parameters.Add(new MySqlParameter("TaskStreamId", taskDetail.TaskStreamId));
                dbCommand.Parameters.Add(new MySqlParameter("Title", taskDetail.Title));
                dbCommand.Parameters.Add(new MySqlParameter("TotalEffort", taskDetail.TotalEffort));

                dbCommand.ExecuteNonQuery();

                dbCommand.CommandText = "SELECT @@Identity";

				int savedId = GetSavedId(dbCommand);

                taskDetail.Id = savedId;
            }
            else
            {
                var dbCommand = new MySqlCommand(
                    "UPDATE TaskDetail SET Description = @Description, MilestoneDate = @MilestoneDate, PercentageComplete = @PercentageComplete, Release = @Release, TaskPersonId = @TaskPersonId, TaskPriorityId = @TaskPriorityId, TaskStreamId = @TaskStreamId, Title = @Title, TotalEffort = @TotalEffort WHERE Id = @Id",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Description", taskDetail.Description));
                dbCommand.Parameters.Add(new MySqlParameter("MilestoneDate", taskDetail.MilestoneDate.ToString("yyyy-MM-dd")));
                dbCommand.Parameters.Add(new MySqlParameter("PercentageComplete", taskDetail.PercentageComplete));
                dbCommand.Parameters.Add(new MySqlParameter("Release", taskDetail.Release));
                dbCommand.Parameters.Add(new MySqlParameter("TaskPersonId", taskDetail.TaskPersonId));
                dbCommand.Parameters.Add(new MySqlParameter("TaskPriorityId", taskDetail.TaskPriorityId));
                dbCommand.Parameters.Add(new MySqlParameter("TaskStreamId", taskDetail.TaskStreamId));
                dbCommand.Parameters.Add(new MySqlParameter("Title", taskDetail.Title));
                dbCommand.Parameters.Add(new MySqlParameter("TotalEffort", taskDetail.TotalEffort));
                dbCommand.Parameters.Add(new MySqlParameter("Id", taskDetail.Id));

                dbCommand.ExecuteNonQuery();
            }

            return taskDetail.Id;
        }

        public int SaveTaskPerson(TaskPerson taskPerson, IEnumerable<int> allowedProjects)
        {
            if (taskPerson.Id == 0)
            {
                // Step one, save the person
                var dbCommand = new MySqlCommand(
                    "INSERT INTO TaskPerson (Email, FirstName, LastName, PassText, TaskRoleId) VALUES (@Email, @FirstName, @LastName, @PassText, @TaskRoleId)",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Email", taskPerson.Email));
                dbCommand.Parameters.Add(new MySqlParameter("FirstName", taskPerson.FirstName));
                dbCommand.Parameters.Add(new MySqlParameter("LastName", taskPerson.LastName));
                dbCommand.Parameters.Add(new MySqlParameter("PassText", taskPerson.Password));
                dbCommand.Parameters.Add(new MySqlParameter("TaskRoleId", taskPerson.TaskRoleId));

                dbCommand.ExecuteNonQuery();
                dbCommand.Parameters.Clear();

                // Step two, get their id
                dbCommand.CommandText = "SELECT @@Identity";

				int savedId = GetSavedId(dbCommand);

                taskPerson.Id = savedId;
            }
            else
            {
                // Step one, save the person
                var dbCommand = new MySqlCommand(
                   "UPDATE TaskPerson SET Email = @Email, FirstName = @FirstName, LastName = @LastName, PassText = @PassText, TaskRoleId = @TaskRoleId WHERE Id = @Id",
                   _dbConnection
                   );

                dbCommand.Parameters.Add(new MySqlParameter("Email", taskPerson.Email));
                dbCommand.Parameters.Add(new MySqlParameter("FirstName", taskPerson.FirstName));
                dbCommand.Parameters.Add(new MySqlParameter("LastName", taskPerson.LastName));
                dbCommand.Parameters.Add(new MySqlParameter("PassText", taskPerson.Password));
                dbCommand.Parameters.Add(new MySqlParameter("TaskRoleId", taskPerson.TaskRoleId));
                dbCommand.Parameters.Add(new MySqlParameter("Id", taskPerson.Id));

                dbCommand.ExecuteNonQuery();
                
                // Step two - we already know their id
            }

            var dbCommand2 = new MySqlCommand(
                "DELETE * FROM LinkProjectToPerson WHERE TaskPersonId = @TaskPersonId ",
                _dbConnection);

            // Step three, clear out the linked projects
            dbCommand2.Parameters.Add(new MySqlParameter("TaskPersonId", taskPerson.Id));

            dbCommand2.ExecuteNonQuery();
            dbCommand2.Parameters.Clear();

            // Step four, add linked projects
            foreach (var projectId in allowedProjects)
            {
                dbCommand2.CommandText = "INSERT INTO LinkProjectToPerson(TaskProjectId, TaskPersonId) VALUES (@TaskProjectId, @TaskPersonId) ";
                dbCommand2.Parameters.Add(new MySqlParameter("TaskProjectId", projectId));
                dbCommand2.Parameters.Add(new MySqlParameter("TaskPersonId", taskPerson.Id));
                dbCommand2.ExecuteNonQuery();
                dbCommand2.Parameters.Clear();
            }

            return taskPerson.Id;
        }

        public int SaveTaskPriority(TaskPriority taskPriority)
        {
            if (taskPriority.Id == 0)
            {
                var dbCommand = new MySqlCommand(
                    "INSERT INTO TaskPriority (Title, Importance) VALUES (@Title, @Importance)",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Title", taskPriority.Title));
                dbCommand.Parameters.Add(new MySqlParameter("Importance", taskPriority.Importance));

                dbCommand.ExecuteNonQuery();

                dbCommand.CommandText = "SELECT @@Identity";

				int savedId = GetSavedId(dbCommand);

                taskPriority.Id = savedId;
            }
            else
            {
                var dbCommand = new MySqlCommand(
                    "UPDATE TaskPriority SET Title = @Title, Importance = @Importance WHERE Id = @Id",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Title", taskPriority.Title));
                dbCommand.Parameters.Add(new MySqlParameter("Importance", taskPriority.Importance));
                dbCommand.Parameters.Add(new MySqlParameter("Id", taskPriority.Id));

                dbCommand.ExecuteNonQuery();
            }

            return taskPriority.Id;
        }

        public int SaveTaskRole(TaskRole taskRole)
        {
            if (taskRole.Id == 0)
            {
                var dbCommand = new MySqlCommand(
                    "INSERT INTO TaskRole (Title) VALUES (@Title)",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Title", taskRole.Title));

                dbCommand.ExecuteNonQuery();

                dbCommand.CommandText = "SELECT @@Identity";

				int savedId = GetSavedId(dbCommand);

                taskRole.Id = savedId;
            }
            else
            {
                var dbCommand = new MySqlCommand(
                    "UPDATE TaskRole SET Title = @Title WHERE Id = @Id",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Title", taskRole.Title));
                dbCommand.Parameters.Add(new MySqlParameter("Id", taskRole.Id));

                dbCommand.ExecuteNonQuery();
            }

            return taskRole.Id;
        }

        public int SaveTaskStream(TaskStream taskStream)
        {
            if (taskStream.Id == 0)
            {
                var dbCommand = new MySqlCommand(
                    "INSERT INTO TaskStream (Title, OrderBy, TaskProjectId, ShowTasks) VALUES (@Title, @OrderBy, @TaskProjectId, @ShowTasks)",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Title", taskStream.Title));
                dbCommand.Parameters.Add(new MySqlParameter("OrderBy", taskStream.Order));
                dbCommand.Parameters.Add(new MySqlParameter("TaskProjectId", taskStream.TaskProjectId));
                dbCommand.Parameters.Add(new MySqlParameter("ShowTasks", taskStream.ShowTasks ? 1 : 0));

                dbCommand.ExecuteNonQuery();

                dbCommand.CommandText = "SELECT @@Identity";

				int savedId = GetSavedId(dbCommand);

                taskStream.Id = savedId;
            }
            else
            {
                var dbCommand = new MySqlCommand(
                    "UPDATE TaskStream SET Title = @Title, OrderBy = @OrderBy, TaskProjectId = @TaskProjectId, ShowTasks = @ShowTasks WHERE Id = @Id",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Title", taskStream.Title));
                dbCommand.Parameters.Add(new MySqlParameter("OrderBy", taskStream.Order));
                dbCommand.Parameters.Add(new MySqlParameter("TaskProjectId", taskStream.TaskProjectId));
                dbCommand.Parameters.Add(new MySqlParameter("ShowTasks", taskStream.ShowTasks ? 1 : 0));
                dbCommand.Parameters.Add(new MySqlParameter("Id", taskStream.Id));

                dbCommand.ExecuteNonQuery();
            }

            return taskStream.Id;
        }

        public int SaveTaskProject(TaskProject taskProject)
        {
            if (taskProject.Id == 0)
            {
                var dbCommand = new MySqlCommand(
                    "INSERT INTO TaskProject (Title, OrderBy) VALUES (@Title, @OrderBy)",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Title", taskProject.Title));
                dbCommand.Parameters.Add(new MySqlParameter("OrderBy", taskProject.Order));

                dbCommand.ExecuteNonQuery();

                dbCommand.CommandText = "SELECT @@Identity";

				int savedId = GetSavedId(dbCommand);

                taskProject.Id = savedId;
            }
            else
            {
                var dbCommand = new MySqlCommand(
                    "UPDATE TaskProject SET Title = @Title, OrderBy = @OrderBy WHERE Id = @Id",
                    _dbConnection);

                dbCommand.Parameters.Add(new MySqlParameter("Title", taskProject.Title));
                dbCommand.Parameters.Add(new MySqlParameter("OrderBy", taskProject.Order));
                dbCommand.Parameters.Add(new MySqlParameter("Id", taskProject.Id));

                dbCommand.ExecuteNonQuery();
            }

            return taskProject.Id;
        }

        public ExtendedTaskDetail GetTaskDetail(int id)
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskDetail WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            var dataReader = dbCommand.ExecuteReader();

            var extendedTaskDetail = new ExtendedTaskDetail();
            var waitingForRecord = true;

            while (dataReader.Read() && waitingForRecord)
            {
                extendedTaskDetail.Task = new TaskDetail
                {
                    Id = GetIntegerValue(dataReader["Id"]),
                    MilestoneDate = (DateTime)dataReader["MilestoneDate"],
                    PercentageComplete = GetIntegerValue(dataReader["PercentageComplete"]),
                    Release = dataReader["Release"].ToString(),
                    TaskPersonId = GetIntegerValue(dataReader["TaskPersonId"]),
                    TaskPriorityId = GetIntegerValue(dataReader["TaskPriorityId"]),
                    TaskStreamId = GetIntegerValue(dataReader["TaskStreamId"]),
                    Title = dataReader["Title"].ToString(),
                    Description = dataReader["Description"].ToString(),
                };
                waitingForRecord = false;
            }

            dataReader.Close();

            extendedTaskDetail.Person = GetTaskPerson(extendedTaskDetail.Task.TaskPersonId);
            extendedTaskDetail.Priority = GetTaskPriority(extendedTaskDetail.Task.TaskPriorityId);
            extendedTaskDetail.Stream = GetTaskStream(extendedTaskDetail.Task.TaskStreamId);

            return extendedTaskDetail;
        }

        public TaskPerson GetTaskPerson(int id)
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskPerson WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            var dataReader = dbCommand.ExecuteReader();

            var taskPeople = GetTaskPeopleFromReader(dataReader);

            return taskPeople[0];
        }

        public TaskPerson GetTaskPersonByEmail(string email)
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskPerson WHERE Email = @Email",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Email", email));

            var dataReader = dbCommand.ExecuteReader();

            var taskPeople = GetTaskPeopleFromReader(dataReader);

            var taskPerson = new TaskPerson();

            if (taskPeople.Count > 0)
            {
                taskPerson = taskPeople[0];
            }

            return taskPerson;
        }

        public TaskPriority GetTaskPriority(int id)
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskPriority WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            var dataReader = dbCommand.ExecuteReader();

            var taskPriorities = GetTaskPrioritiesFromReader(dataReader);

            return taskPriorities[0];
        }

        public TaskStream GetTaskStream(int id)
        {
            var dbCommand = new MySqlCommand(
                "SELECT TaskStream.*, TaskProject.Title `TaskProjectTitle` FROM (TaskStream INNER JOIN TaskProject ON TaskProject.Id = TaskStream.TaskProjectId) WHERE TaskStream.Id = @Id ",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            var dataReader = dbCommand.ExecuteReader();

            var taskStreams = GetTaskStreamsFromReader(dataReader);

            return taskStreams[0];
        }

        public TaskProject GetTaskProject(int id)
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskProject WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            var dataReader = dbCommand.ExecuteReader();

            var taskProjects = GetTaskProjectsFromReader(dataReader);

            return taskProjects[0];
        }

        public TaskRole GetTaskRole(int id)
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskRole WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            var dataReader = dbCommand.ExecuteReader();

            var taskProjects = GetTaskRolesFromReader(dataReader);

            return taskProjects[0];
        }

        public void DeleteTaskPriority(int id)
        {
            var dbCommand = new MySqlCommand(
                "DELETE FROM TaskPriority WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            dbCommand.ExecuteNonQuery();
        }

        public void DeleteTaskStream(int id)
        {
            var dbCommand = new MySqlCommand(
                "DELETE FROM TaskStream WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            dbCommand.ExecuteNonQuery();
        }

        public void DeleteTaskProject(int id)
        {
            var dbCommand = new MySqlCommand(
                "DELETE FROM TaskProject WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            dbCommand.ExecuteNonQuery();
        }

        public void DeleteTaskPerson(int id)
        {
            var dbCommand = new MySqlCommand(
                "DELETE FROM TaskPerson WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            dbCommand.ExecuteNonQuery();
        }

        public void DeleteTaskDetail(int id)
        {
            var dbCommand = new MySqlCommand(
                "DELETE FROM TaskDetail WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            dbCommand.ExecuteNonQuery();
        }

        public void DeleteTaskRole(int id)
        {
            var dbCommand = new MySqlCommand(
                "DELETE FROM TaskRole WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            dbCommand.ExecuteNonQuery();
        }

        public void Dispose()
        {
            if (_dbConnection.State != ConnectionState.Closed)
            {
                _dbConnection.Close();
            }
            _dbConnection.Dispose();
        }

        public List<TaskAudit> GetAllTaskAudits()
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskAudit ORDER BY Id DESC",
                _dbConnection);

            var dataReader = dbCommand.ExecuteReader();

            var taskAudits = GetTaskAuditsFromReader(dataReader);

            return taskAudits;
        }

        public List<TaskAudit> GetAllTaskAuditsForSearch(int searchId)
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskAudit WHERE TaskPersonId = @SearchTerm OR ItemId = @SearchTerm ORDER BY TaskAudit.Id DESC",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("SearchTerm", searchId));

            var dataReader = dbCommand.ExecuteReader();

            var taskAudits = GetTaskAuditsFromReader(dataReader);

            return taskAudits;
        }

        public TaskAudit GetTaskAudit(int id)
        {
            var dbCommand = new MySqlCommand(
                "SELECT * FROM TaskAudit WHERE Id = @Id",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("Id", id));

            var dataReader = dbCommand.ExecuteReader();

            var taskPeople = GetTaskAuditsFromReader(dataReader);

            return taskPeople[0];
        }

        public int SaveTaskAudit(TaskAudit taskAudit)
        {
            var dbCommand = new MySqlCommand(
                "INSERT INTO TaskAudit (AuditFor, ItemAfter, ItemId, TaskPersonId, AuditDate, Title) VALUES (@AuditFor, @ItemAfter, @ItemId, @TaskPersonId, @AuditDate, @Title)",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("AuditFor", taskAudit.AuditFor));
            dbCommand.Parameters.Add(new MySqlParameter("ItemAfter", taskAudit.ItemAfter));
            dbCommand.Parameters.Add(new MySqlParameter("ItemId", taskAudit.ItemId));
            dbCommand.Parameters.Add(new MySqlParameter("TaskPersonId", taskAudit.TaskPersonId));
            dbCommand.Parameters.Add(new MySqlParameter("AuditDate", taskAudit.AuditDate.ToString("yyyy-MM-dd hh:mm:ss")));
            dbCommand.Parameters.Add(new MySqlParameter("Title", taskAudit.Title));

            dbCommand.ExecuteNonQuery();

            dbCommand.CommandText = "SELECT @@Identity";

        	int savedId = GetSavedId(dbCommand);

        	taskAudit.Id = savedId;

            return taskAudit.Id;
        }

		private static bool GetBooleanValue(object input)
		{
			bool booleanValue = input.ToString().Equals("1");
			return booleanValue;
		}

		private static int GetIntegerValue(object input)
		{
			int integerValue;
			int.TryParse(input.ToString(), out integerValue);
			return integerValue;
		}

		private static int GetSavedId(MySqlCommand dbCommand)
		{
			var resultAsInteger = 0;
			if (dbCommand != null)
			{
				var result = dbCommand.ExecuteScalar();
				int.TryParse(result.ToString(), out resultAsInteger);
			}
			return resultAsInteger;
		}

		public void DeleteTaskAudits(DateTime date)
        {
            var dbCommand = new MySqlCommand(
                "DELETE FROM TaskAudit  WHERE AuditDate < @AuditDate",
                _dbConnection);

            dbCommand.Parameters.Add(new MySqlParameter("AuditDate", date));

            dbCommand.ExecuteNonQuery();
        }

        private static List<TaskDetail> GetTaskDetailFromReader(IDataReader dataReader)
        {
            var taskDetails = new List<TaskDetail>();

            while (dataReader.Read())
            {
                var taskDetail = new TaskDetail
                {
                    Id = GetIntegerValue(dataReader["Id"]),
                    MilestoneDate = (DateTime)dataReader["MilestoneDate"],
                    PercentageComplete = GetIntegerValue(dataReader["PercentageComplete"]),
                    Release = dataReader["Release"].ToString(),
                    TaskPersonId = GetIntegerValue(dataReader["TaskPersonId"]),
                    TaskPriorityId = GetIntegerValue(dataReader["TaskPriorityId"]),
                    TaskStreamId = GetIntegerValue(dataReader["TaskStreamId"]),
                    Title = dataReader["Title"].ToString(),
                    Description = dataReader["Description"].ToString(),
                    TaskProjectId = GetIntegerValue(dataReader["TaskProjectId"])
                };

                taskDetails.Add(taskDetail);
            }

			dataReader.Close();

            return taskDetails;
        }

        private static List<TaskPerson> GetTaskPeopleFromReader(IDataReader dataReader)
        {
            var taskPeople = new List<TaskPerson>();
            while (dataReader.Read())
            {
                var taskPerson = new TaskPerson
                {
                    Id = GetIntegerValue(dataReader["Id"]),
                    Email = dataReader["Email"].ToString(),
                    FirstName = dataReader["FirstName"].ToString(),
                    LastName = dataReader["LastName"].ToString(),
                    Password = dataReader["PassText"].ToString(),
                    TaskRoleId = GetIntegerValue(dataReader["TaskRoleId"])
                };

                taskPeople.Add(taskPerson);
            }

			dataReader.Close();

            return taskPeople;
        }

        private static List<TaskAudit> GetTaskAuditsFromReader(IDataReader dataReader)
        {
            var taskAudits = new List<TaskAudit>();
            while (dataReader.Read())
            {
                var taskAudit = new TaskAudit
                {
                    Id = GetIntegerValue(dataReader["Id"]),
                    Title = dataReader["Title"].ToString(),
                    AuditFor = dataReader["AuditFor"].ToString(),
                    ItemAfter = dataReader["ItemAfter"].ToString(),
                    ItemId = GetIntegerValue(dataReader["ItemId"]),
                    TaskPersonId = GetIntegerValue(dataReader["TaskPersonId"]),
                    AuditDate = (DateTime)dataReader["AuditDate"]
                };

                taskAudits.Add(taskAudit);
            }

			dataReader.Close();

            return taskAudits;
        }

        private static List<TaskPriority> GetTaskPrioritiesFromReader(IDataReader dataReader)
        {
            var taskPriorities = new List<TaskPriority>();
            while (dataReader.Read())
            {
                var taskPriority = new TaskPriority
                {
                    Id = GetIntegerValue(dataReader["Id"]),
                    Importance = GetIntegerValue(dataReader["Importance"]),
                    Title = dataReader["Title"].ToString()
                };

                taskPriorities.Add(taskPriority);
            }

			dataReader.Close();

            return taskPriorities;
        }

        private static List<TaskRole> GetTaskRolesFromReader(IDataReader dataReader)
        {
            var taskRoles = new List<TaskRole>();
            while (dataReader.Read())
            {
                var taskPriority = new TaskRole
                {
                    Id = GetIntegerValue(dataReader["Id"]),
                    Title = dataReader["Title"].ToString()
                };

                taskRoles.Add(taskPriority);
            }

			dataReader.Close();

            return taskRoles;
        }

        private static List<TaskProject> GetTaskProjectsFromReader(IDataReader dataReader)
        {
            var taskProjects = new List<TaskProject>();

            while (dataReader.Read())
            {
                var taskProject = new TaskProject
                {
                    Id = GetIntegerValue(dataReader["Id"]),
                    Title = dataReader["Title"].ToString(),
                    Order = GetIntegerValue(dataReader["OrderBy"])
                };

                taskProjects.Add(taskProject);
            }

			dataReader.Close();

            return taskProjects;
        }

        private static List<TaskStream> GetTaskStreamsFromReader(IDataReader dataReader)
        {
            var taskStreams = new List<TaskStream>();

            while (dataReader.Read())
            {
                var taskStream = new TaskStream
                {
                    Id = GetIntegerValue(dataReader["Id"]),
                    TaskProjectId = GetIntegerValue(dataReader["TaskProjectId"]),
                    TaskProjectTitle = dataReader["TaskProjectTitle"].ToString(),
                    Title = dataReader["Title"].ToString(),
                    Order = GetIntegerValue(dataReader["OrderBy"]),
                    ShowTasks = GetBooleanValue(dataReader["ShowTasks"])
                };

                taskStreams.Add(taskStream);
            }

			dataReader.Close();

            return taskStreams;
        }
	}
}